#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年8月28日

@author: chenyongbing
'''
import sys
sys.path.append('../')

from base.PyMySQL import ConnectMySQL
import re,datetime

def showTables():
    tabs = []
    query = 'show tables;'
    #print query
    ret = myConnectMySQL.SelectAll(query)
    for tab_list in ret:
        tab = tab_list[0]
        tabs.append(tab)
    return  tabs


def make_select_query_by_group(tabs,group,st,et):
    for tab in tabs:
        if tab in ('mac_customer','mac_employee','mac_walkby'):continue
        ret = myConnectMySQL.descTab(tab)
        #print ret  
        #print '--table : %s --'%tab
        if 'dgroup' in ret:
            if 'day' in ret:
                query = 'delete from %s where dgroup=%s and day between "%s" and "%s";'%(tab,group,st,et)
            elif 'last_time' in ret:
                query = 'delete from %s where dgroup=%s and last_time between "%s" and "%s";'%(tab,group,st,et)
            print query
        if 'any_group' in ret:
            if 'day' in ret:
                query = 'delete from %s where any_group=%s and day between "%s" and "%s";'%(tab,group,st,et)
            elif 'last_time' in ret:
                query = 'delete from %s where any_group=%s and last_time between "%s" and "%s";'%(tab,group,st,et)
            print query


def change_group(tabs,ogroup,ngroup,st=None,et=None,type='insert'):
    for tab in tabs:
        if tab not in ('mac_customer','mac_employee','mac_walkby'):continue
    
        ret = myConnectMySQL.descTab(tab)
        if 'id' in ret:ret.remove('id')
        fields = ','.join(ret)
        if 'dgroup' in ret:
            groupField = 'dgroup'
            nfields = re.sub('dgroup',str(ngroup),fields)
        if 'any_group' in ret :
            groupField = 'any_group'
            nfields = re.sub('any_group',str(ngroup),fields)
        if 'anyGroup' in ret:
            groupField = 'anyGroup'
            nfields = re.sub('anyGroup',str(ngroup),fields)
        if 'day' in ret:
            dayField = 'day'
        if 'last_time' in ret:
            dayField = 'last_time'
        if type=='update':
            if st and et:
                query = 'update %s set %s="%s" where %s="%s" and %s between "%s" and "%s";'%(tab,groupField,ngroup,groupField,ogroup,dayField,st,et)
            else:
                query = 'update %s set %s="%s" where %s="%s"'%(tab,groupField,ngroup,groupField,ogroup)
        elif type=='delete':
            if st and et:
                query = 'delete from %s where %s=%s and %s between "%s" and "%s";'%(tab,groupField,ogroup,dayField,st,et)
        elif type=='copy':
            query = 'insert into %s( '%tab
            query = query + fields 
            query = query + ') select '+nfields + ' from %s where %s="%s" and %s between "%s" and "%s"'%(tab,groupField,ogroup,dayField,st,et)+';'
        
        
        print query 






def update_tab_from_other_database(tabs,db='zhimind',rerun_db='zhimind_reload',comp_group=None,group=None,groups=[],st=None,et=None,run_type='update',timeType='day'):
    cmdList = []
    database1 = db
    database2 = rerun_db
    err = 0
    
    if timeType == 'week':
        st_date = datetime.date(int(st[:4]),int(st[5:7]),int(st[8:10]))
        weeknum =  st_date.weekday()
        st = (st_date - datetime.timedelta(weeknum)).strftime('%Y-%m-%d')
    elif timeType == 'month':
        st = st[:7]+'-01'
    
    elif timeType == 'quarter':
        month = str((int(st[5:7])/4)*3+1)
        st = st[:5]+'0'*(2-len(month))+month+'-01'
    
    
    for tab in tabs:
        #if tab in ('mac_customer','mac_employee','mac_walkby'):continue
        
        if tab in ('mac_customer','mac_walkby'):continue
        if timeType=='day':
            if re.search('circle|week|month|quarter|demo',tab):continue
            if re.search('day|hour|mac_customer|mac_walkby|mac_employee',tab):
                #print tab
                pass
            else:
                continue
            
            
        elif timeType == 'week':
            if not re.search('week',tab):continue
        elif timeType == 'month':
            if not re.search('month',tab):continue
        elif timeType=='quarter':
            if not re.search('quarter',tab):continue
        else:pass
        ret = myConnectMySQL.descTab(tab)
        
        if 'id' in ret:ret.remove('id')
    
        fields = ','.join(ret)
        
        if group==None and len(groups)==0:
            if 'day' in ret:
                dayField = 'day'
            elif 'last_time' in ret:
                dayField = 'last_time'
            if run_type=='update':
                query = 'insert  into %s.%s (%s) select %s from %s.%s'%(database1,tab,fields,fields,database2,tab)
                if st and et:
                    if st==et:
                        query = query + ' where %s.%s.%s="%s"'%(database2,tab,dayField,et)
                    else:
                        query = query + ' where %s.%s.%s between "%s" and "%s"'%(database2,tab,dayField,st,et)
                
                
            elif run_type=='delete':
                query = 'delete from %s.%s'%(database1,tab)
                if st and et:
                    if st==et:
                        query = query + ' where %s.%s.%s="%s"'%(database1,tab,dayField,st)
                    else:
                        
                        query = query + ' where %s.%s.%s between "%s" and "%s"'%(database1,tab,dayField,st,et)
            if tab=='mac_employee':
                query = query + ' and type=0' 
            
            query = query + ';'
            print query
            continue
        
        if 'dgroup' in ret:
            groupField = 'dgroup'
            #nfields = re.sub('dgroup',str(ngroup),fields)
        elif 'any_group' in ret :
            groupField = 'any_group'
            #nfields = re.sub('any_group',str(ngroup),fields)
        elif 'anyGroup' in ret:
            groupField = 'anyGroup'
            #nfields = re.sub('anyGroup',str(ngroup),fields)
        else:
            #print "*"*10,tab
            err +=1
            continue
        if 'day' in ret:
            dayField = 'day'
        elif 'last_time' in ret:
            dayField = 'last_time'
        if run_type=='update':
            if group!=None:
                query = 'insert ignore into %s.%s (%s) select %s from %s.%s where %s.%s.%s="%s"'%(database1,tab,fields,fields,database2,tab,database2,tab,groupField,group)
            elif comp_group!=None and 'comp_group' in ret:
                query = 'insert ignore into %s.%s (%s) select %s from %s.%s where %s.%s.comp_group="%s"'%(database1,tab,fields,fields,database2,tab,database2,tab,comp_group)
            elif len(groups)!=0:
                query = 'insert ignore into %s.%s (%s) select %s from %s.%s where %s.%s.%s in (%s)'%(database1,tab,fields,fields,database2,tab,database2,tab,groupField,','.join(groups))
            if st and et:
                query = query + ' and %s.%s.%s between "%s" and "%s"'%(database2,tab,dayField,st,et)
            
            
        elif run_type=='delete':
            if group!=None:
                query = 'delete from %s.%s where %s.%s.%s=%s'%(database1,tab,database1,tab,groupField,group)
            elif comp_group !=None and 'comp_group' in ret:
                query = 'delete from %s.%s where %s.%s.comp_group="%s"'%(database1,tab,database1,tab,comp_group)
                
            elif len(groups)!=0:
                query = 'delete from %s.%s where %s.%s.%s in (%s)'%(database1,tab,database1,tab,groupField,",".join(groups))
            if st and et:
                query = query + ' and %s.%s.%s between "%s" and "%s"'%(database1,tab,dayField,st,et)
        
        if tab=='mac_employee':
            query = query + ' and type=0'
        query = query + ';'
            
        cmdList.append( query )
    return cmdList


def delete_from_mac_customer_walkby(db='zhimind',groups=[]):
    cmdList = []
    cmdList.append( 'delete from %s.mac_customer where dgroup in (%s);'%(db,','.join(groups)))
    cmdList.append(  'delete from %s.mac_walkby where dgroup in (%s);'%(db,','.join(groups)))
    return cmdList

def reload_from_mac_customer_walkby_stats(day=None,db='zhimind',comp_group=None,groups=[]):
    cmdList = []
    if day==None:
        day = (datetime.date.today()-datetime.timedelta(1)).strftime('%Y-%m-%d')
    
    history_start = (datetime.date(int(day[:4]),int(day[5:7]),int(day[8:10]))-datetime.timedelta(179)).strftime('%Y-%m-%d')
    history_end = day
    
    if comp_group!=None:
        query1 = 'insert into mac_customer(dgroup,mac,role,last_time) \
            select dgroup,mac,max(role),max(day) from mac_customer_stats \
                where comp_group=%s and  day between "%s" and "%s" group by dgroup,mac;'%(comp_group,history_start,history_end)
        query2 =  'insert into mac_walkby(dgroup,mac,role,last_time) \
            select dgroup,mac,max(role),max(day) from mac_walkby_stats \
                where comp_group=%s and  day between "%s" and "%s" group by dgroup,mac;'%(comp_group,history_start,history_end)
        
        cmdList = [query1,query2]
    return cmdList
def getInsertQuery(tabs,groups=[],st=None,et=None):
    fr = open('../db/insert_tab.sql','w')
    for tab in tabs:
        if tab in ('mac_customer','mac_employee','mac_walkby','mac_factory'):continue
        ret = myConnectMySQL.descTab(tab)
        if 'id' in ret:ret.remove('id')
        fields = ','.join(ret)
        if 'dgroup' in ret:
            groupField = 'dgroup'
            
        elif 'any_group' in ret :
            groupField = 'any_group'
            
        elif 'anyGroup' in ret:
            groupField = 'anyGroup'
        
        else:
            #print "*"*10
            continue  
        
        if 'day' in ret:
            dayField = 'day'
        if 'last_time' in ret:
            dayField = 'last_time'
  
        for group in groups:
            #print group
            query = 'select %s from %s where %s="%s"'%(fields,tab,groupField,group)
            if st!=None and et!=None:
                query = query + ' and %s between "%s" and "%s"'%(dayField,st,et)
            query = query + ';'
            #print query
            datas = myConnectMySQL.SelectAll(query)
            if not datas:
                #print query
                continue
            insertQuery = 'insert %s (%s) VALUES '%(tab,fields)
            for data in  datas:
                ndata = ''
                for d in data:
                    ndata+='"%s",'%d
                ndata = re.sub(',$','',ndata)
                insertQuery+='(%s),'%ndata
            insertQuery = re.sub(',$',';',insertQuery)
            fr.write(insertQuery + '\n')
            print insertQuery
    fr.close()   
    
    
def dump_tab_with_comp_group(tabs,comp_group,groups,db='zhimind',user='root',passwd='zhimatech',host='localhost',port=3306,tmpPath = '/data/tmp/',dumpType='all'):
    cmdList = []
    day = datetime.date.today().strftime('%Y-%m-%d')
    cmd = 'mysqldump --single-transaction '
        
    if host!='localhost':
        cmd+=' -h%s'%host
    if port!=3306:
        cmd += ' --port %s'%port
    cmd += ' -u%s -p%s'%(user,passwd)
    opt_file = '%s/%s/zhimind_opt.sql'%(tmpPath,day)
    if dumpType == 'all':
        db_cmd = cmd+' -d %s > %s'%(db,opt_file)
        cmdList.append( db_cmd)
    
    
    
    for tab in tabs:
        if tab in ('mac_customer','mac_walkby'):continue
        if tab in ('circle','circle_followed','circle_mac','circle_mac_change',
                   'day_circle_mac_stat','demo_customer_info','month_circle_mac_stat',
                   'quarter_circle_mac_stat','week_circle_mac_stat'
                   ):continue
        fieldList = myConnectMySQL.descTab(tab)
        sql_file = '%s/%s/%s.%s.sql'%(tmpPath,day,tab,comp_group)
        
        tab_cmd='%s -t %s %s'%(cmd,db,tab)
        if 'comp_group' in fieldList:
            tab_cmd += ''' --where="comp_group='%s'"'''%(comp_group)
            
        elif 'dgroup' in fieldList:
            tab_cmd +=''' --where="dgroup in (%s)"'''%(','.join(groups))
        elif 'any_group' in fieldList:
            tab_cmd +=''' --where="any_group in (%s)"'''%(','.join(groups))
        
        tab_cmd+= ' > %s'%sql_file
        cmdList.append( tab_cmd)
    return cmdList    
def reload_tab(tabs,db='zhimind',user='root',passwd='zhimatech',host='localhost',port=3306,tmpPath = '/data/tmp/',dumpType='all'):        
    cmdList = []
    day = datetime.date.today().strftime('%Y-%m-%d')
    cmd = 'mysql'
        
    if host!='localhost':
        cmd+=' -h%s'%host
    if port!=3306:
        cmd += ' --port %s'%port
    cmd += ' -u%s -p%s %s'%(user,passwd,db)
    opt_file = '%s/%s/zhimind_opt.sql'%(tmpPath,day)
    if dumpType == 'all':
        cmdList.append( '%s < %s'%(cmd,opt_file))
    
    for tab in tabs:
        if tab in ('mac_customer','mac_walkby'):continue
        if tab in ('circle','circle_followed','circle_mac','circle_mac_change',
                   'day_circle_mac_stat','demo_customer_info','month_circle_mac_stat',
                   'quarter_circle_mac_stat','week_circle_mac_stat'
                   ):continue
                   
        sql_file = '%s/%s/%s.%s.sql'%(tmpPath,day,tab,comp_group)
        
            
        tab_cmd= '%s < %s'%(cmd,sql_file)
        cmdList.append( tab_cmd)
    return cmdList
        


from base.retail_datas import myRetailDatas


if __name__=='__main__':
    import argparse
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--run',metavar="dump_reload or dump or reload or delete or update or delete_update",default='dump_reload')
    parser.add_argument('--db_config',metavar="zhimind host",default='zm09:3306:zhimind')
    parser.add_argument('--rerun_db_config',metavar="zhimind host",default='zm08:3307:zhimind_rerun')
    parser.add_argument('--reload_db_config',metavar="zhimind host",default='zm09:3306:zhimind_reload')
    parser.add_argument('--startTime',metavar="delete and update start time",default=None)
    parser.add_argument('--endTime',metavar="delete and update end time",default=None)
    parser.add_argument('--comp_id',metavar="comp_id",default=None)
    parser.add_argument('--dumpType',metavar="dump type:all,data,opt",default='all')
    
    
    args = parser.parse_args()

    runType = args.run
    db_config = args.db_config
    rerun_db_config = args.rerun_db_config
    reload_db_config = args.reload_db_config
    st = args.startTime
    et = args.endTime
    comp_id = args.comp_id
    dumpType = args.dumpType
    
    
    host,port,db = db_config.split(':')
    rerun_host,rerun_port,rerun_db = rerun_db_config.split(':')
    reload_host,reload_port,reload_db = reload_db_config.split(':')
    
    
    myConnectMySQL = ConnectMySQL(host=host,user='root',password='zhimatech',db=db)
    comp_group  = myRetailDatas.get_comp_group(comp_id=comp_id)
    
    ogroups = myRetailDatas.get_groups_by_comp( comp_id=comp_id)
    groups = []
    for g in ogroups:
        groups.append(str(g))

    tabs = showTables()
    cmdList = []

    if runType=='dump_reload':
        dump_cmd_list =dump_tab_with_comp_group(tabs,comp_group,groups,host=host,dumpType=dumpType)
        reload_cmd_list = reload_tab(tabs,host=rerun_host,db=rerun_db,port=rerun_port,dumpType=dumpType)
        cmdList=cmdList + dump_cmd_list + reload_cmd_list
        
    elif runType == 'delete_update' or runType=='delete' or runType == 'update':
        if runType == 'delete' or runType == 'delete_update':
            delete_cmd_list = delete_from_mac_customer_walkby(db=db,groups=groups)
        else:
            delete_cmd_list = []
        cmdList+=delete_cmd_list
        
        for run_type in runType.split('_'):
            day_cmd_list = update_tab_from_other_database(tabs,db=db,rerun_db=reload_db,comp_group=comp_group,groups=groups,run_type=run_type,st=st,et=et,timeType='day')
            week_cmd_list = update_tab_from_other_database(tabs,db=db,rerun_db=reload_db,comp_group=comp_group,groups=groups,run_type=run_type,st=st,et=et,timeType='week')
            month_cmd_list = update_tab_from_other_database(tabs,db=db,rerun_db=reload_db,comp_group=comp_group,groups=groups,run_type=run_type,st=st,et=et,timeType='month')
            quarter_cmd_list = update_tab_from_other_database(tabs,db=db,rerun_db=reload_db,comp_group=comp_group,groups=groups,run_type=run_type,st=st,et=et,timeType='quarter')
            cmdList=cmdList+day_cmd_list+week_cmd_list+month_cmd_list+quarter_cmd_list
        if runType == 'update' or runType == 'delete_update':
            reload_cmd_list = reload_from_mac_customer_walkby_stats(db=db,comp_group=comp_group)
        else:
            reload_cmd_list = []
        cmdList+=reload_cmd_list
    else:
        print 'You Must Enter para with delete or update or dump_reload'
        
    today = datetime.date.today().strftime('%Y-%m-%d')
    save_file = 'log/%s.%s.%s.%s.sql'%(runType,comp_id,today,dumpType)
    
    fr = open(save_file,'w')
    fr.write('\n'.join(cmdList))
    fr.close()
    
    for line in cmdList:
        print line
        
    
